package curtain.whynot.frame.basic.csv.excle;


import com.whynot.frame.basic.util.StringUtils;
import curtain.whynot.frame.basic.csv.parser.AbstractParser;
import org.apache.poi.hssf.usermodel.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.*;
import java.text.DateFormat;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;

public class ExcelWriter extends AbstractParser {

	private static final Logger logger = LoggerFactory.getLogger(ExcelWriter.class);


	public ExcelWriter() {
		super("ExcelWriter");
		this.setEncoding("gbk");
	}


	@Override
	public List<String[]> parseRows(File input) {
		 InputStream is;
		try {
			is = new FileInputStream(input);

			HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
			List<String[]> list = new ArrayList<String[]>(10000);
			 // 循环工作表Sheet
	        for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
	            HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
	            if (hssfSheet == null) {
	                continue;
	            }
	            int celNum =  hssfSheet.getRow(0).getPhysicalNumberOfCells();
	            // 循环行Row
	            for (int rowNum = 0; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
	                HSSFRow hssfRow = hssfSheet.getRow(rowNum);
	                if (hssfRow == null) {
	                    continue;
	                }
	                String [] arr = new String[celNum];
	                for(int i = 0;i<celNum;i++){
	                	arr[i] = getValue(hssfRow.getCell(i));
	                }
	                list.add(arr);
	            }
	        }
	        return list;

		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
	}

	@Override
	public File writeRows(List<Object[]> rows) {
		File tmpFile;
		try {
			tmpFile = File.createTempFile(UUID.randomUUID().toString(), ".xls");
			writeRows(rows, tmpFile.getAbsolutePath());
		} catch (IOException e) {
			logger.error(e.getMessage());
			return null;
		}
		return tmpFile;
	}
	@Override
	public File writeRows(List<Object[]> rows, String filepath) {
		File file = new File(filepath);
		try {
			writeRows(rows,file);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return file;
	}


	public void writeRows(List<Object[]> rows ,File file) throws Exception  {
		FileOutputStream fout = new FileOutputStream(file);
		// 第一步，创建一个webbook，对应一个Excel文件
		HSSFWorkbook wb = new HSSFWorkbook();
		// 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet
		HSSFSheet sheet = wb.createSheet(file.getName());
		// 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
		HSSFRow row = sheet.createRow((int) 0);
		// 第四步，创建单元格，并设置值表头 设置表头居中
		HSSFCellStyle style = wb.createCellStyle();
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
		for (int j = 0 ; j<rows.get(0).length ; j++){
			HSSFCell cell = row.createCell((short) j);
			cell.setCellValue(StringUtils.asString(rows.get(0)[j]));
    		cell.setCellStyle(style);
		}
	    for (int i = 1; i < rows.size(); i++) {
	    	row = sheet.createRow((int) i);
    		Object[] data = rows.get(i);
	    	for (int j = 0 ; j<data.length ; j++) {
	    		Object o = rows.get(i)[j];
	    		if(o instanceof Integer){
	    			int intValue = (Integer) o;
	    			row.createCell((short) j).setCellValue(intValue);
	    		}  else if (o instanceof Long) {
                   long longValue = (Long) o;
                   row.createCell((short) j).setCellValue(longValue);
                }else if(o instanceof Boolean){
                	 Boolean boolValue = (Boolean) o;
                     row.createCell((short) j).setCellValue(boolValue);
                }else{
                	row.createCell((short) j).setCellValue(StringUtils.asString(o));
                }
	    	}
		}
        wb.write(fout);
        wb.close();
        fout.close();
	}

	@Override
	public void processRows(File input) {
		// TODO Auto-generated method stub

	}

	/**
     * 得到Excel表中的值
     *
     * @param hssfCell
     *            Excel中的每一个格子
     * @return Excel中每一个格子中的值
     */
    @SuppressWarnings("static-access")
    private String getValue(HSSFCell hssfCell) {
        String cellValue = null;
        DecimalFormat df = new DecimalFormat("#");
        if(StringUtils.isNotEmpty(hssfCell)){

        switch (hssfCell.getCellType()) {
        case HSSFCell.CELL_TYPE_STRING:
            cellValue = hssfCell.getRichStringCellValue().getString().trim();
            break;
        case HSSFCell.CELL_TYPE_NUMERIC:
        	if(HSSFDateUtil.isCellDateFormatted(hssfCell)){
        		//用于转化为日期格式
        		Date d = hssfCell.getDateCellValue();
        		DateFormat formater = new SimpleDateFormat("yyyy-MM-dd");
        		cellValue = formater.format(d).toString();
        	}else{
        		cellValue = df.format(hssfCell.getNumericCellValue()).toString();
        	}
            break;
        case HSSFCell.CELL_TYPE_BOOLEAN:
            cellValue = String.valueOf(hssfCell.getBooleanCellValue()).trim();
            break;
        case HSSFCell.CELL_TYPE_FORMULA:
            cellValue = hssfCell.getCellFormula();
            break;
        default:
            cellValue = "";
        }
        }
        return cellValue;
    }




	public static void main(String[] args) throws IOException {
		List<Object[]> rows = Arrays
				.asList(new Object[][] {
						{ "1997", "Ford", "E350", "ac, abs, moon", "3000.00" },
						{ "1999", "Chevy", "Venture \"Extended Edition\"", "",
								"1" },
						{ "1996", "Jeep", "Grand中文 Cherokee",
								"MUST SELL!\nair, moon roof, loaded", "4799.00" },
						{},
						{ "1999", "Chevy",
								"Venture \"Extended Edition, Very Large\"",
								null, "5000.00" },
						{ null, "", "Venture \"Extended Edition\"", null,
								"4900.00" }, });
		ExcelWriter ExcelWriter = new ExcelWriter();
		File file = ExcelWriter.writeRows(rows);
		System.out.println(file.getAbsolutePath());
	}

}
